﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_ListMember2')
BEGIN
    PRINT 'Dropping Procedure proc_cm_ListMember2'
    DROP  Procedure  proc_cm_ListMember2
END
GO

PRINT 'Creating Procedure proc_cm_ListMember2'
GO

CREATE PROCEDURE [dbo].[proc_cm_ListMember2]
AS
BEGIN
	SET NOCOUNT ON

	SELECT 
		m.[member_id]
		,m.[UserId]
		,u.[UserName]
		,mm.[Email] 
		,m.[firstname_txt]
		,m.[lastname_txt]
		,m.[korean_nm]
		,m.[address_txt]
		,m.[address2_txt]
		,m.[city_txt]
		,m.[state_cd]
		,m.[zip_cd]
		,m.[country_nm]
		,ISNULL(m.[cellphone_txt], m.[homephone_txt]) AS [phone_num]
		,m.[homephone_txt]
		,m.[cellphone_txt]
		,m.[birthdate_dt]
		,m.[icon_filename_txt]
		,m.[member_since_dt]
		,m.[last_login_dttm]
		,case when m.[confirmation_guid] is null then CONVERT(bit,1) else CONVERT(bit,0) end as [confirmed_ind]
		,m.[created_dttm]
		,STUFF(
                   (SELECT
                        ', ' + sg2.[group_nm]
                        FROM [dbo].[tbl_member_smallgroup] ms2 (nolock)
							inner join [dbo].[tbl_smallgroup] sg2 (nolock) on ms2.[smallgroup_id] = sg2.[smallgroup_id]
						WHERE ms2.[member_id] = m.[member_id]
                        ORDER BY sg2.[group_nm]
                        FOR XML PATH(''), TYPE
                   ).value('.','nvarchar(max)')
                   ,1,2, ''
              ) AS [group_nm]
	FROM 
		[dbo].[tbl_member] m (NOLOCK)
		INNER JOIN [dbo].[aspnet_Users] u (NOLOCK) on m.[UserId] = u.[UserId]
		INNER JOIN [dbo].[aspnet_Membership] mm (NOLOCK) on m.[UserId] = mm.[UserId]
		LEFT JOIN [dbo].[tbl_member_smallgroup] ms (NOLOCK) on m.[member_id] = ms.[member_id]
		LEFT JOIN [dbo].[tbl_smallgroup] sg (NOLOCK) on ms.[smallgroup_id] = sg.[smallgroup_id]
	GROUP BY
		m.[member_id]
		,m.[UserId]
		,u.[UserName]
		,mm.[Email] 
		,m.[firstname_txt]
		,m.[lastname_txt]
		,m.[korean_nm]
		,m.[address_txt]
		,m.[address2_txt]
		,m.[city_txt]
		,m.[state_cd]
		,m.[zip_cd]
		,m.[country_nm]
		,m.[cellphone_txt]
		,m.[homephone_txt]
		,m.[homephone_txt]
		,m.[cellphone_txt]
		,m.[birthdate_dt]
		,m.[icon_filename_txt]
		,m.[member_since_dt]
		,m.[last_login_dttm]
		,m.[confirmation_guid]
		,m.[created_dttm]
END
GO

GRANT EXEC ON dbo.proc_cm_ListMember2 TO PUBLIC
GO

